/*
Use Master
GO
Drop database BugReportingSystem
GO
*/

Create database BugReportingSystem
GO

Use BugReportingSystem
GO
/*

*/
Create table [User]
(
	FullName nvarchar(30) not null,
	UserName nvarchar(20) Primary Key,
	Password varchar(20) not null,
	Res nvarchar(30) not null,
	Status bit not null,
	/*
		Status = 1: Active (default)
		Status = 0: Inactive
	*/
)
GO

Create table Bug
(
	BugName nvarchar(30) Primary Key,
	Description nvarchar(50),
	Charge money not null,
	Warranty bit not null
	/*
		Warranty = 1: Yes (default)
		Warranty = 0: No
	*/
)
GO

Create table Warranty
(
	WarrantyName nvarchar(30) Primary Key,
	Description nvarchar(50),
	WarrantyMonth int not null
)
GO

Create table WarrantyDetail
(
	Bug nvarchar(30) not null References Bug(BugName),
	Description nvarchar(50),
	Warranty nvarchar(30) not null References Warranty(WarrantyName),
	Constraint WarrantyDetail_PK Primary Key(Bug, Warranty)
)
GO

Create table Unit
(
	UnitName nvarchar(20) Primary Key,
	Description nvarchar(50)
)
GO

Create table Item
(
	ItemName nvarchar(20) Primary Key,
	Description nvarchar(50),
	Unit nvarchar(20) not null References Unit(UnitName),
	Price money not null,
	Warranty nvarchar(30) not null References Warranty(WarrantyName)
)
GO

Create table Customer
(
	CustomerNo int identity(1,1) Primary Key,
	FullName nvarchar(50) not null,
	Alias nvarchar(50) Unique,
	Address nvarchar(50),
	Tel nvarchar(20),
	Email nvarchar(50)
)
GO

Create table [Order]
(
	OrderNo nvarchar(20) Primary Key,
	OrderDate datetime not null,
	Customer nvarchar(50) not null References Customer(Alias),
	DispatchNo int identity(1,1) not null Unique
)
GO

Create table OrderDetail
(
	Item nvarchar(20) not null References Item(ItemName),	
	Quantity int not null,
	Amount money not null,
	WarrantyExpiryDate datetime not null,
	[Order] nvarchar(20) not null References [Order](OrderNo),
	Constraint OrderDetail_PK Primary Key(Item, [Order])
)
GO

Create table Complaint
(
	ComplaintNo int identity(1,1) Primary Key,	
	Description nvarchar(1000) not null,
	ComplaintDate datetime not null,
	Dispatch int not null References [Order](DispatchNo)
)
GO

Create table Status
(
	StatusName nvarchar(30) Primary Key,
	Description nvarchar(50)
)
GO

Create table ComplaintDetail
(
	Bug nvarchar(30) not null References Bug(BugName),
	Description nvarchar(1000) not null,
	ComplaintDate datetime not null,
	Status nvarchar(30) not null References Status(StatusName),
	Dispatch int not null References [Order](DispatchNo),
	Constraint ComplaintDetail_PK Primary Key(Bug, Dispatch)
)
GO